package com.csvw.skr.basic.service;

import com.csvw.skr.basic.entity.Organization;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import com.webonn.frame.core.Pagination;
import com.webonn.frame.db.MyDao;
import com.webonn.frame.util.ListToTreeUtils;
import com.webonn.frame.util.MyStringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * Created by Peng on 2016/12/29.
 */
@Service
public class ResourceService {

    @Autowired
    private MyDao myDao;


    /**
     * 查询要返回组织的树
     *
     * @param code      编号
     * @param firstFlag 是否第一次查询
     * @return
     */
    public List<Map<String, Object>> queryOrganizationTree(String code, int level, boolean firstFlag) {
        List<Object> params = Lists.newArrayList();

        String sql = "select pk_code as code, name, level from csvw_skr_basic_organization organization " +
                "where organization.active = true  and organization.pk_code!= 'Kcc'  and organization.headquarters > 1 %s";

        // 总部首次不触发，之后都触发
        if (level == 1 && firstFlag)
            sql = sql.replace("and organization.headquarters > 1", "");

        if (firstFlag) {
            sql = String.format(sql, "and organization.pk_code=?");
            params.add(code);
        } else {
            sql = String.format(sql, "and organization.pcode= ? and organization.level > ?");
            params.add(code);
            params.add(level);
        }

        return handleOrganizationTree(sql, params);
    }

    /**
     * 点检统计该点检的组织树
     */
    public List<Map<String, Object>> queryInspectionDraftOrganizationTree(String code, int level, boolean firstFlag, String inspectionDraftId) {
        List<Object> params = Lists.newArrayList();

        String sql = "select pk_code as code, name, level from csvw_skr_basic_organization organization " +
                "where organization.active = true  and organization.pk_code!= 'Kcc'  and organization.headquarters > 1 %s ";

        // 总部首次不触发，之后都触发
        if (level == 1 && firstFlag)
            sql = sql.replace("and organization.headquarters > 1", "");

        // 如果首次触发
        if (firstFlag) {
            sql = String.format(sql, "and organization.pk_code=?");
            params.add(code);
        } else {
            sql = String.format(sql, "and organization.pcode= ? and organization.level > ?");
            params.add(code);
            params.add(level);
        }

        if (MyStringUtil.nonNullOrEmpty(inspectionDraftId)) {
            sql += "AND (SELECT plan.area FROM csvw_skr_task_inspection_plan_draft plan WHERE plan.pk_id = ? ) LIKE CONCAT('%',organization.pk_code,'%')";
            params.add(inspectionDraftId);
        }

        return handleOrganizationTree(sql, params);
    }

    private List<Map<String, Object>> handleOrganizationTree(String sql, List<Object> params) {
        List<Map<String, Object>> resultTree = Lists.newArrayList();

        List<Map<String, Object>> result = myDao.queryResultToMap(sql, params.toArray());

        //将查到的数据构造成treeview所需要的模型
        if (result != null && result.size() > 0) {
            result.forEach(organization -> {
                resultTree.add(buildingOrganizationTree(organization));
            });
        }
        return resultTree;
    }

    /**
     * 构造组织-treeview 模型
     *
     * @param organization
     */
    private Map<String, Object> buildingOrganizationTree(Map<String, Object> organization) {
        Map<String, Object> organizationTree = new HashMap<>();
        organizationTree.put("text", organization.get("name"));
        organizationTree.put("code", organization.get("code"));
        List<Map<String, Object>> children = getOrganizationByPcode(organization.get("code").toString());
        if (children != null && children.size() > 0) {
            organizationTree.put("nodes", Lists.newArrayList());
        }

        return organizationTree;
    }

    /**
     * 根据组织父节点的code查询节点
     *
     * @param pcode
     * @return
     */
    private List<Map<String, Object>> getOrganizationByPcode(String pcode) {
        String sql = "select pk_code as code, name, level from csvw_skr_basic_organization organization " +
                "where organization.active = true and organization.pcode=? and organization.headquarters > 1 ";

        List<Map<String, Object>> result = myDao.queryResultToMap(sql, new Object[]{pcode});

        return result;
    }

    /*******************************************************************************************
     * 带经销商的树
     */
    public List<Map<String, Object>> queryDealerTree(String code, int level, boolean firstFlag) {


        List<Map<String, Object>> resultTree = Lists.newArrayList();

        String sql = "select pk_code as code, name, level from csvw_skr_basic_organization organization " +
                "where organization.active = true and organization.pk_code!= 'Kcc' and organization.headquarters > 1 %s";

        // 总部首次不触发，之后都触发
        if (level == 1 && firstFlag)
            sql = sql.replace("and organization.headquarters > 1", "");

        sql = firstFlag ? String.format(sql, "and organization.pk_code=?") : String.format(sql, "and organization.pcode=? and organization.level > ?");

        List<Map<String, Object>> result = myDao.queryResultToMap(sql, firstFlag ? new Object[]{code} : new Object[]{code, level});

        //查询出对象for循环调用下面方法构造要返回的对象
        if (result != null && result.size() > 0) {
            result.forEach(organization -> {
                resultTree.add(buildingDealerOrgTree(organization));
            });
        } else {
            //如果没有组织了，就查经销商，构造成treeview模型返回

            // TODO 经销商和商务经理
            // List<Map<String, Object>> dealerList = queryDealerByBusinessManager(this.getUserIdByOrganization(code));
            List<Map<String, Object>> dealerList = queryDealerByArea(code);
            if (dealerList != null && dealerList.size() > 0) {
                dealerList.forEach(dealer -> {
                    resultTree.add(buildingDealerTree(dealer));
                });
            }
        }
        return resultTree;
    }

    /**
     * 构造经销商组织-treeview 模型
     *
     * @param organization
     */
    private Map<String, Object> buildingDealerOrgTree(Map<String, Object> organization) {
        Map<String, Object> organizationTree = new HashMap<>();

        String code = (String) organization.get("code");
        String name = (String) organization.get("name");

        organizationTree.put("code", code);

        //如果是小区 构造成  "冀一区/张三" 样式
        if ((int) organization.get("level") == 4) {

            Object userName = queryUserNameByOrg(code);
            if (userName == null) {
                organizationTree.put("text", name);
            } else {
                organizationTree.put("text", name + " / " + userName);
            }
        } else {
            organizationTree.put("text", name);
        }

        List<Map<String, Object>> children = getOrganizationByPcode(organization.get("code").toString());

        if (children != null && children.size() > 0) {
            organizationTree.put("nodes", Lists.newArrayList());
        } else {
            List<Map<String, Object>> dealerList = queryDealerByArea(organization.get("code").toString());

            if (dealerList != null && dealerList.size() > 0) organizationTree.put("nodes", Lists.newArrayList());
        }

        return organizationTree;
    }

    /**
     * 构造经销商-treeview 模型
     *
     * @param dealer
     * @return
     */
    private Map<String, Object> buildingDealerTree(Map<String, Object> dealer) {
        Map<String, Object> dealerTree = new HashMap<>();
        dealerTree.put("text", dealer.get("name") + " / " + dealer.get("contract"));
        dealerTree.put("code", dealer.get("code"));
        dealerTree.put("shorter", dealer.get("shorter"));
        return dealerTree;
    }

    /**
     * 根据小区编号查询经销商列表
     *
     * @param area 所属小区编号
     * @return
     */
    private List<Map<String, Object>> queryDealerByArea(String area) {
        String sql = "select pk_code as code,shorter, name,contract from csvw_skr_basic_dealer dealer " +
                "where dealer.active = true and dealer.area=?";

        List<Map<String, Object>> result = myDao.queryResultToMap(sql, new Object[]{area});

        return result;
    }

    private List<Map<String, Object>> queryDealerByBusinessManager(String businessManager) {
        String sql = "select pk_code as code,shorter, name,contract from csvw_skr_basic_dealer dealer " +
                "where dealer.active = true and dealer.businessManager = ? ";
        return myDao.queryResultToMap(sql, new Object[]{businessManager});
    }

    private String getUserIdByOrganization(String organization) {
        String sql = "select pk_empno from csvw_skr_basic_user where organization = ? ";
        return (String) myDao.uniqueResult(sql, new Object[]{organization});
    }

    /**
     * 根据组织Id查询用户名称
     */
    private Object queryUserNameByOrg(String org) {
        String sql = "select group_concat(name) from csvw_skr_basic_user where organization = ? and active = true";
        return myDao.uniqueResult(sql, new Object[]{org});
    }

    /**
     * 查询经销商详情
     *
     * @param organization 和组织id的list
     * @param dealers      经销商id
     * @return
     */
    public List<Object> getDealerInfoByDealerIdOrOrgId(List<String> organization, String dealers) {
        List list = Lists.newArrayList();

        if (dealers != null && !dealers.isEmpty()) {
            String dealers_sql = "SELECT dealer.shorter, organization2. NAME AS rboName, organization1. NAME AS areaName, dealer.contract,dealer.pk_code AS code, " +
                    // TODO 经销商和商务经理
                    "(SELECT group_concat(name) FROM csvw_skr_basic_user WHERE job = 107 and organization = dealer.area and active = true) as businessManagerName, " +
                    // "(SELECT group_concat(name) FROM csvw_skr_basic_user WHERE job = 107 and pk_empno = dealer.businessManager and active = true) as businessManagerName, " +
                    "(SELECT group_concat(name) FROM csvw_skr_basic_user WHERE job = 112 and dealer.marketingManager LIKE CONCAT('%', pk_empno, '%') and active = true) as marketingManagerName, " +
                    "( SELECT group_concat(NAME) FROM csvw_skr_basic_user WHERE job = 115 and dealer.area LIKE CONCAT('%',organization,'%') and  active = true ) AS valueAdderBusinessManagerName, " +
                    "( SELECT group_concat(NAME) FROM csvw_skr_basic_user WHERE job = 111 and dealer.area LIKE CONCAT('%',organization,'%')  and active = true ) AS keyCustomerManagerName " +
                    "FROM csvw_skr_basic_dealer dealer " +
                    "LEFT JOIN csvw_skr_basic_organization organization1 ON organization1.pk_code = dealer.area " +
                    "LEFT JOIN csvw_skr_basic_organization organization2 ON organization2.pk_code = dealer.rbo " +
                    "WHERE ? like CONCAT('%',dealer.pk_code ,'%') and dealer.active = true ";
            list.addAll(myDao.queryResultToMap(dealers_sql, new Object[]{dealers}));
        }

        organization.forEach(id -> {
            String sql = "SELECT dealer.shorter, organization2. NAME AS rboName, organization1. NAME AS areaName, dealer.contract,dealer.pk_code AS code, " +
                    // TODO 经销商和商务经理
                    "(SELECT group_concat(name) FROM csvw_skr_basic_user WHERE job = 107 and organization = dealer.area and active = true) as businessManagerName, " +
                    // "(SELECT group_concat(name) FROM csvw_skr_basic_user WHERE job = 107 and pk_empno = dealer.businessManager and active = true) as businessManagerName, " +
                    "(SELECT group_concat(name) FROM csvw_skr_basic_user WHERE job = 112 and dealer.marketingManager LIKE CONCAT('%', pk_empno, '%') and active = true) as marketingManagerName, " +
                    "( SELECT group_concat(NAME) FROM csvw_skr_basic_user WHERE job = 115 and dealer.area LIKE CONCAT('%',organization,'%') and  active = true ) AS valueAdderBusinessManagerName, " +
                    "( SELECT group_concat(NAME) FROM csvw_skr_basic_user WHERE job = 111 and dealer.area LIKE CONCAT('%',organization,'%')  and active = true ) AS keyCustomerManagerName " +
                    "FROM csvw_skr_basic_dealer dealer " +
                    "LEFT JOIN csvw_skr_basic_organization organization1 ON organization1.pk_code = dealer.area " +
                    "LEFT JOIN csvw_skr_basic_organization organization2 ON organization2.pk_code = dealer.rbo " +
                    "where dealer.active = true ";
            //int size =  id.split("-").length;
            Organization dbOrganization = myDao.get(id, Organization.class);
            int level = dbOrganization.getLevel();
            List<String> paramsList = Lists.newArrayList();
            if (level == 1) {
                sql += "and dealer.area like ? ";
                paramsList.add(id + "%");
            } else if (level == 2) {
                sql += "and dealer.rbo = ? "; //rob
                paramsList.add(id);
            } else if (level == 3) {
                sql += "and dealer.commercial = ? ";//commercial
                paramsList.add(id);
            } else if (level == 4) {
                sql += "and dealer.area = ? ";//area
                paramsList.add(id);
            }
            list.addAll(myDao.queryResultToMap(sql, paramsList.toArray()));

        });
        return list;
    }

    public Pagination queryOrganizationLevelSelection(int start, int limit, String search, int level, String pcode) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "select name as text, pk_code as id from csvw_skr_basic_organization " +
                "where level = ? and headquarters !=1 and pk_code!= 'Kcc' and active =  true ";
        List<Object> paramsList = Lists.newArrayList();
        paramsList.add(level);

        if (level != 1 && level != 2) {
            sql += "and pcode = ? ";
            paramsList.add(pcode);
        }

        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, description) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        return myDao.queryResultToMapPagination(sql, p, paramsList.toArray());
    }


    public Pagination queryDicRegionSelection(int start, int limit, String search, int level) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "select CONCAT(name,enname) as text, id as id from csvw_skr_basic_region where pid = ? ";
        List<Object> paramsList = Lists.newArrayList();
        paramsList.add(level);

        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, enname, shortname) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        return myDao.queryResultToMapPagination(sql, p, paramsList.toArray());
    }

    public Pagination queryMenuLevelSelection(int start, int limit, String search, int level) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "select name as text, pk_code as id from csvw_skr_basic_menu where level = ? ";
        List<Object> paramsList = Lists.newArrayList();
        paramsList.add(level);

        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, description,pc_code) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        return myDao.queryResultToMapPagination(sql, p, paramsList.toArray());
    }

    /**
     * 获取用户端相应父菜单的子菜单
     */
    public List<Map<String, Object>> queryUserMenuByPcode(String userId, String pcode, int maxLevel, boolean isApp, boolean isAppJump) {
        String roles = this.getUserRole(userId);
        if (roles == null || roles.isEmpty()) {
            return Lists.newArrayList();
        }

        String sql = "select distinct(pk_code) as code, name, name as text, url,pcode,tabId,groups FROM csvw_skr_basic_menu menu " +
                "LEFT JOIN csvw_skr_basic_menu_role role_menu ON role_menu.menu = menu.pk_code " +
                "WHERE ? LIKE concat('%', role_menu.role, '%') AND menu.active = true AND menu.app = ? @queryTerm ORDER BY menu.groups ASC, menu.xh ASC";
        List<Object> params = Lists.newArrayList();
        params.add(roles);
        params.add(isApp);

        String sqlTerm = "";

        if (maxLevel != 0) {
            sqlTerm += "and menu.level <= ? ";
            params.add(maxLevel);
        }

        if (isAppJump){
            sqlTerm += " AND menu.pk_code != 'skr_report_app_charts' ";

            List<Map<String, Object>> menus = myDao.queryResultToMap(sql.replace("@queryTerm", sqlTerm), params.toArray());

            return ListToTreeUtils.getIstance("children").constructDTOToTree(menus, pcode);
        } else {

            List<Map<String, Object>> menus = myDao.queryResultToMap(sql.replace("@queryTerm", sqlTerm), params.toArray());

            return ListToTreeUtils.getIstance("nodes").constructDTOToTree(menus, pcode);
        }
    }

    /**
     * 查询用户角色
     */
    private String getUserRole(String userId) {
        String sql = "select u.role from csvw_skr_basic_user u where u.pk_empno = ?";
        return (String) myDao.uniqueResult(sql, new Object[]{userId});
    }

    public Pagination queryUserDealerSelection(int start, int limit, String search, String userId) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "SELECT dealer.pk_code AS id, dealer.shorter AS text FROM `csvw_skr_basic_dealer` dealer " +
                // TODO 经销商和商务经理
                "LEFT JOIN csvw_skr_basic_user u ON u.organization = dealer.area " +
                "WHERE u.pk_empno = ? and dealer.active = true";
        // "WHERE dealer.businessManager = ? and dealer.active = true";
        List<Object> paramsList = Lists.newArrayList();
        paramsList.add(userId);

        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, shorter,pk_code) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        return myDao.queryResultToMapPagination(sql, p, paramsList.toArray());
    }

    public Pagination queryUserSelection(int start, int limit, String search, String type, String value) {
        Pagination p = new Pagination();
        p.setStart(start);
        p.setLimit(limit);
        String sql = "SELECT u.pk_empno AS id, u.`name` AS text FROM `csvw_skr_basic_user` u WHERE u.active = true ";
        List<Object> paramsList = Lists.newArrayList();

        switch (type) {
            case "all":
                break;
            case "organization":
                sql += "and u.organization = ?";
                paramsList.add(value);
                break;
            case "job":
                sql += "and u.job = ?";
                paramsList.add(value);
                break;
            default:
                return null;
        }

        if (!MyStringUtil.isNullOrEmpty(search)) {
            sql += " and CONCAT_WS(',', name, pk_empno, phone) like ? ";
            paramsList.add("%" + search.trim() + "%");
        }
        return myDao.queryResultToMapPagination(sql, p, paramsList.toArray());
    }

    public Integer queryDicPidByName(String pid) {
        String sql = "SELECT region.id FROM `csvw_skr_basic_region` region WHERE ? LIKE CONCAT('%',region.`name`,'%') ";
        Object o = myDao.uniqueResult(sql, new Object[]{pid});
        return (Integer) o;
    }

    public List<Map<String, Object>> listModelTree() {
        String sql = "SELECT modelName 'code', modelName 'text' FROM skoda_sksr_report_model_type GROUP BY modelName";

        List<Map<String, Object>> list = Lists.newArrayList();
        Map<String, Object> map = Maps.newHashMap();
        map.put("code", "All");
        map.put("text", "All");
        map.put("nodes", myDao.queryResultToMap(sql, new Object[]{}));
        list.add(map);
        return list;
    }
}
